-- common..p_InsSaleOrder 'TUTU','1507',67.0,34.0,1.0,4556.0,5345.0,5345.0,2922218.4,'CMV'
use common
go
IF OBJECT_ID('dbo.p_InsSaleOrder') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_InsSaleOrder
    IF OBJECT_ID('dbo.p_InsSaleOrder') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_InsSaleOrder >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_InsSaleOrder >>>'
END
go
create proc p_InsSaleOrder
(
	@ItemCode			varchar(50),	-- ItemCode
	@InvoiceNumber		varchar(30),	-- ItemName
	@ItemName           varchar(30),	--	TEAK,PALLAVE,ETC   VT
	@ItemClass      	varchar(10),	--	 class A,B etc AB
	@SalesPrice      	decimal(10,4)	,
	@Length				decimal(10,4),	-- Length
	@Width				decimal(10,4),	-- Width
	@Measurement		varchar(30),	-- Measurement
	@Quantity			decimal(10,4),	-- Quantity
	@Units				decimal(10,4),	-- Quantity
	@UnitPrice			decimal(10,4),	-- Autowrap participant
	@ExtendedPrice		decimal(10,4),	-- PurchasePrice
	@Tax				decimal(10,4),	-- Tax
	@Total				decimal(10,4),  -- Total
	@Amount				decimal(10,4),  -- Amount
	@CompanyName		varchar(50)	-- CompanyName  data belongs to which CMP

) as
begin
	-- declare
    declare @DateUpdated	datetime	-- datetime when this row was updated
    declare @UserUpdated	varchar(15)	-- user name that updated this row
	declare @Msg 		varchar(255)	-- last error message
	declare @ErrVal		int		-- last error number
	declare @ItemId		int	
	declare @LotNumber	varchar(50)
	declare @ItemType	varchar(50)
	declare @Supplier	varchar(50)
	-- initialize
	select @ErrVal=0
	select @DateUpdated=getdate()
	select @UserUpdated=suser_name()
	select @ItemId=0
	if @ItemId=0
	begin tran p_InsSaleOrder
		select @ErrVal=0
		if @ErrVal=0
		begin
  print 'insert t_SalesOrder started'
  				select  @LotNumber =LotNumber,@Supplier	=PurchasedFrom from t_ItemMaster where 
  				ItemCode =@ItemCode and CompanyName = @CompanyName
				insert into common..t_SalesOrder 
				( ItemCode,InvoiceNumber,ItemName,ItemClass,SalesPrice,Length,
				  Width,Measurement,Quantity,Units,UnitPrice,ExtendedPrice,SalesDate,RequestedDate,
				  TransactionDate ,Tax,CompanyName,LotNumber,Total,Amount,Supplier )
				values(@ItemCode,@InvoiceNumber,@ItemName,@ItemClass,@SalesPrice,@Length,
				  @Width,@Measurement,@Quantity,@Units,@UnitPrice,@ExtendedPrice,@DateUpdated,@DateUpdated,
				  @DateUpdated ,@Tax,@CompanyName,@LotNumber,@Total,@Amount,@Supplier)
				  
		-- remove the length from t_IteMaster
		select @ItemType = Measurement from t_ItemMaster  where ItemCode =@ItemCode and CompanyName = @CompanyName
		if (@ItemType ='TIMBER')
		begin
		update t_ItemMaster set Length =(Length - @Length) where ItemCode =@ItemCode and CompanyName = @CompanyName
		end
		-- decarseing item count from item master
		 update t_ItemMaster set Units =(Units - @Units) where ItemCode =@ItemCode and CompanyName = @CompanyName
				select @ErrVal=@@error
		end
		if @ErrVal!=0
		begin
			rollback tran p_InsSaleOrder
			select @ItemId=0
			select @ErrVal=99999,@Msg="Failed to insert new SalesOrder:" + @ItemCode
			goto ErrorHandler
		end
	commit tran p_InsSaleOrder
    --end

	if @ErrVal!=0
	begin
			select @ErrVal=99999, @Msg="Failed to insert ItemCode:" + @ItemCode
			goto ErrorHandler
	end
Done:

	return 0 

ErrorHandler:
	raiserror @ErrVal @Msg
end
go
EXEC sp_procxmode 'dbo.p_InsSaleOrder','unchained'
go
IF OBJECT_ID('dbo.p_InsSaleOrder') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_InsSaleOrder >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_InsSaleOrder >>>'
go
GRANT EXECUTE ON dbo.p_InsSaleOrder TO public
go